﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class ModelColumnExtendConfigDAL : DB
    {
        public ModelColumnExtendConfigDAL(LoginInfo info)
        {
            base.loginInfo = info;
        }
        /// <summary>
        /// 删除旧数据
        /// </summary> 
        public void BeforeInsert(string modelTableID)
        {
            HIDB.dbo con = base.conn();
            con.ExecuteSql("delete from [Working_db].[ColumnExpression] where parentguid in (select guid FROM [Working_db].[Model_Column_ExtendConfig] where [ModelTableID]=@ModelTableID)",
                new SqlParameter[] { new SqlParameter("@ModelTableID", modelTableID) });
            con.ExecuteSql("DELETE FROM [Working_db].[Model_Column_ExtendConfig] where [ModelTableID]=@ModelTableID", new SqlParameter[] { new SqlParameter("@ModelTableID", modelTableID) });
        }

        public string SaveAndCreateView(DataTable dt, DataTable dtOpts, string modelID)
        {
            string result = string.Empty;
            try
            {
                HIDB.dbo con = base.conn();
                SqlParameter[] modelPara = new SqlParameter[] { new SqlParameter("@ModelID", modelID) };
                //检查引用的模型字段是否都存在
                DataTable mdt = con.GetDataTable(" select ColumnName from [Working_db].[Model_ColumnConfig] where ModelTableID=@ModelID", modelPara);
                foreach (DataRow vdr in dt.Rows) {
                    string viewCol = vdr["ColumnScriptExtend"].ToString().ToLower();
                    bool ext = false;
                    foreach (DataRow mdr in mdt.Rows)
                    {
                        if(viewCol.IndexOf(mdr[0].ToString().ToLower())>-1)//
                        {
                            ext = true;
                        }
                    }
                    if (!ext)
                        return "操作失败！字段：" + vdr["ColumnName"].ToString()+" 引用了不存在的字段，请检查脚本配置。";
                }
                
                //创建历史记录
                DataTable dtStep = con.StoredDataTable("[Working_db].[CreateModelRecord]", modelPara);

                ModelConfigDAL dal = new ModelConfigDAL(loginInfo);

                this.BeforeInsert(modelID);//删除旧数据
                if (dt.Rows.Count > 0)
                {
                    dt.Columns.Remove("SourceSysName");
                    dt.Columns.Remove("ColumnSourceID");
                    dt.Columns.Remove("TableName");
                    dt.Columns.Remove("FactColumnName");
                    dt.Columns.Remove("PK");
                    dt.Columns.Remove("Operator");
                    dt.Columns.Remove("ColumnExpression");
                    dt.Columns.Remove("IsValue");
                    SqlParameter[] para = new SqlParameter[]
                    {
                        new SqlParameter("@ModelID",modelID)
                       ,new SqlParameter("@ViewColumn",dt)  
                       ,new SqlParameter("@Expression",dtOpts)    
                    };
                    ///添加视图
                    con.StoredUpdate(@"[Working_db].[UP_Model_Column_ExtendConfig_Insert]", para);//保存数据

                    //创建物理表
                    if (dtStep != null && dtStep.Rows.Count > 0)
                    {
                        if (Convert.ToInt32(dtStep.Rows[0][0]) == 6)
                        {
                            ////创建物理表
                            con.StoredUpdate("[Working_db].[CreateModel_CheckBack]", new SqlParameter[] { new SqlParameter("@Model_id_p", modelID) });
                        }
                    }
                }
                ///更新步骤
                con.ExecuteSql("Update [Working_db].[Model_Config] set Step=5 where GUID=@GUID and Step=4",
                    new SqlParameter[] { new SqlParameter("@GUID", modelID) });
                result = "1";
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }

        public bool Update(ModelColumnExtendConfig data)
        {
            bool result = true;
            try
            {

                HIDB.dbo con = base.conn();
                SqlParameter[] sqlparams = new SqlParameter[]
                    { 
                         new  SqlParameter("@GUID",data.GUID)
                        ,new  SqlParameter("@ColumnName",data.ColumnName)
                        ,new  SqlParameter("@ColumnScriptExtend",data.ColumnScriptExtend)
                        ,new  SqlParameter("@UpdateUser","")
                        ,new  SqlParameter("@UpdateTime",DateTime.Now)
                     };
                con.StoredUpdate("[Working_db].[UP_Model_Column_ExtendConfig_Update]", sqlparams);
            }
            catch (Exception)
            {
                result = false;
                throw;
            }
            return result;
        }

        public bool ColumnNameIsExist(string guid, string modelTableID, string columnName)
        {
            bool result = true;
            try
            {

                HIDB.dbo con = base.conn();
                SqlParameter[] sqlparams = new SqlParameter[]
                    { 
                          new  SqlParameter("@GUID",guid)
                         ,new  SqlParameter("@ModelTableID",modelTableID)
                         ,new  SqlParameter("@ColumnName",columnName)
                     };
                result = int.Parse(con.StoredDataTable("[Working_db].[UP_Model_Column_ExtendConfig_ColumnNameIsExist]", sqlparams).Rows[0]["RowCount"].ToString()) > 0 ? true : false;
            }
            catch (Exception)
            {
                result = false;
                throw;
            }
            return result;

        }

        public bool Delete(string guid)
        {
            bool result = true;
            try
            {

                HIDB.dbo con = base.conn();
                SqlParameter[] sqlparams = new SqlParameter[]
                    { 
                         new  SqlParameter("@GUID",guid)
                     };
                con.StoredUpdate("[Working_db].[UP_Model_Column_ExtendConfig_Delete]", sqlparams);
            }
            catch (Exception)
            {
                result = false;
                throw;
            }
            return result;
        }

        public IList<ModelColumnExtendConfig> GetList(string modelTableID)
        {
            IList<ModelColumnExtendConfig> list = new List<ModelColumnExtendConfig>();
            try
            {
                //创建数据库对象
                HIDB.dbo con = base.conn();
                //添加输入输出参数
                SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@ModelTableID", modelTableID) };
                DataTable dt = con.StoredDataTable("[Working_db].[UP_Model_Column_ExtendConfig_GetList]", sqlparams);
                list = Common.DBConvert.DataTableToList<ModelColumnExtendConfig>(dt);
            }
            catch (Exception)
            {

                throw;
            }
            return list;
        }

        public ModelColumnExtendConfig LoadByGuid(string guid)
        {
            ModelColumnExtendConfig result = new ModelColumnExtendConfig();
            try
            {
                //创建数据库对象
                HIDB.dbo con = base.conn();
                //添加输入输出参数
                SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@GUID", guid) };
                DataTable dt = con.StoredDataTable("[Working_db].[UP_Model_Column_ExtendConfig_LoadByGUID]", sqlparams);
                if (dt != null && dt.Rows.Count > 0)
                {
                    result.GUID = dt.Rows[0]["GUID"].ToString();
                    result.ColumnName = dt.Rows[0]["ColumnName"].ToString();
                    result.ColumnScriptExtend = dt.Rows[0]["ColumnScriptExtend"].ToString();
                }
            }
            catch (Exception)
            {

                throw;
            }
            return result;
        }
    }
}
